using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;

namespace nsDb
{

    public class DatabaseControl
    {

      
        


        


        


        private string _ConnectionString;
        public string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        } //eof property FieldName 


        public DatabaseControl(string connectionStr)
        {
            this.ConnectionString = connectionStr;
        } //eof constructor 


        //callMe 	public bool LookUpColValueById ( ref  msg,  ref  colMetaId , ref  colId , ref  colMetaValue , ref  colValue , 	ref  tableName)
        public bool LookUpColValueById(ref string msg, ref string colMetaId, ref string colId, ref string colMetaValue,
            ref string colValue, ref string tableName)
        {


            try
            {
                using (Database db = new Database(_ConnectionString))
                using (DbTransaction txn = db.BeginTransaction())
                {
                    string sqlStringCommand_str = "SELECT " + colMetaValue + " from " + tableName +
                        " where " + colMetaId + " = '" + colId + "'";
                    Utils.Debugger.WriteIf(sqlStringCommand_str);

                    DbCommand cmd = db.GetSqlStringCommand(sqlStringCommand_str);

                    cmd.Transaction = txn;
                    DataSet ds = db.ExecuteDataSet(cmd);
                    colValue = System.Convert.ToString(ds.Tables[0].Rows[0][colMetaValue]);
                    Utils.Debugger.WriteIf("The colValue is " + colValue);
                    txn.Commit();
                    msg = String.Empty;
                    return true;
                } //eof using


            } //eof try
            catch (System.InvalidOperationException e) //comm -- occurs when no result set was found !!!
            {
                msg = "An error in the application occurred. Report to Advanced Application Support the following error: Error while catching Look up values for dropdown: ";
                Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
                return false;
            } //eof catch (System.InvalidOperationException)

            catch (System.IndexOutOfRangeException e) //comm -- occurs when no result set was found !!!
            {
                msg = "An error in the application occurred. Report to Advanced Application Support the following error: Error while catching Look up values for dropdown: ";
                Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
                return false;
            } //eof catch (System.IndexOutOfRangeException)

            catch (Exception e)
            {
                Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
                msg = "An error in the application occurred. Report to Advanced Application Support the following error: Error while catching Look up values for dropdown: \n " + e.Message;
                return false;
            } //eof catch

        } //eof method  LookUpColValue 


        //callMe 	LookUpColIdByValue ( ref  msg, ref  colMetaId, ref  colId, ref  colMetaValue, 		ref  colValue, ref  tableName )
        public bool LookUpColIdByValue(ref string msg, ref string colMetaId, ref string colId, ref string colMetaValue,
            ref string colValue, ref string tableName)
        {
            try
            {
                using (Database db = new Database(_ConnectionString))
                using (DbTransaction txn = db.BeginTransaction())
                {
                    string sqlStringCommand_str = "SELECT " + colMetaId + " from " + tableName +
                        " where " + colMetaValue + " = '" + colValue + "'";
                    Utils.Debugger.WriteIf(sqlStringCommand_str);

                    DbCommand cmd = db.GetSqlStringCommand(sqlStringCommand_str);

                    cmd.Transaction = txn;
                    DataSet ds = db.ExecuteDataSet(cmd);
                    colId = System.Convert.ToString(ds.Tables[0].Rows[0][colMetaId]);
                    Utils.Debugger.WriteIf("The colId is " + colId);
                    txn.Commit();
                    msg = String.Empty;
                    return true;
                } //eof using


            } //eof try
            catch (System.InvalidOperationException e) //comm -- occurs when no result set was found !!!
            {
                msg = "An error in the application occurred. Report to Advanced Application Support the following error: Error while catching Look up values for dropdown: ";
                Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
                return false;
            } //eof catch (System.InvalidOperationException)

            catch (System.IndexOutOfRangeException e) //comm -- occurs when no result set was found !!!
            {
                msg = "An error in the application occurred. Report to Advanced Application Support the following error: Error while catching Look up values for dropdown: ";
                Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
                return false;
            } //eof catch (System.IndexOutOfRangeException)

            catch (Exception e)
            {
                Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
                msg = "An error in the application occurred. Report to Advanced Application Support the following error: Error while catching Look up values for dropdown: \n " + e.Message;
                return false;
            } //eof catch

        } //eof method  LookUpColValue 


        //callMe public bool ListColValuesForLookUp ( ref msg, ref ds, ref colMetaValue, ref tableName )
        public bool ListColValuesForLookUp(ref string msg, ref DataSet ds, ref string colMetaValue, ref string tableName)
        {

            try
            {
                using (Database db = new Database(_ConnectionString))
                using (DbTransaction txn = db.BeginTransaction())
                {
                    string sqlStringCommand_str = "SELECT " + colMetaValue + " from " + tableName;
                    Utils.Debugger.WriteIf(sqlStringCommand_str);

                    DbCommand cmd = db.GetSqlStringCommand(sqlStringCommand_str);

                    cmd.Transaction = txn;
                    ds = db.ExecuteDataSet(cmd);
                    txn.Commit();
                    msg = String.Empty;
                    return true;
                } //eof using


            } //eof try
            catch (System.InvalidOperationException e) //comm -- occurs when no result set was found !!!
            {
                msg = "An error in the application occurred. Report to Advanced Application Support the following error: Error while catching Look up values for dropdown: ";
                Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
                return false;
            } //eof catch (System.InvalidOperationException)

            catch (System.IndexOutOfRangeException e) //comm -- occurs when no result set was found !!!
            {
                msg = "An error in the application occurred. Report to Advanced Application Support the following error: Error while catching Look up values for dropdown: ";
                Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
                return false;
            } //eof catch (System.IndexOutOfRangeException)

            catch (Exception e)
            {
                Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
                msg = "An error in the application occurred. Report to Advanced Application Support the following error: Error while catching Look up values for dropdown: \n " + e.Message;
                return false;
            } //eof catch


        } //eof method ListColValuesForLookUp

    } //eof public class DatabaseControl


} //eof namespace


